<!DOCTYPE html>
<html lang="en"><head>
    <title>CZH-DEV BLOG</title>
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type">
    <meta content="utf-8" http-equiv="encoding">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="format-detection" content="telephone=no" />
    <meta name="theme-color" content="#000084" />
    <link rel="icon" href="https://czh-dev.gitee.io/czh-blog.gitee.io//favicon.ico">
    <link rel="canonical" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">
    
    
</head>
<body>
<nav class="navbar navbar-inverse navbar-fixed-top">
    <div class="navbar-inner">
        <div class="container">
            <button type="button" class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse"></button>
            <a class="brand" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">CZH-DEV BLOG</a>
            <div class="nav-collapse collapse">
                <ul class="nav">
                    
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/about/">
                                    
                                    <span>About</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/post/">
                                    
                                    <span>All posts</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/ebook/">
                                    
                                    <span>Resource</span>
                                </a>
                            </li>
                        
                    
                </ul>
            </div>
        </div>
    </div>
</nav><div id="content" class="container">
<div style="display: flex;">
  <div class="row-fluid navmargin">
    <div class="page-header">
      <h1>MySQL数据库基础操作 - Fri, Feb 10, 2023</h1>
    </div>
    <p class="lead"></p>
    <h1 id="数据库基础操作">数据库基础操作</h1>
<h2 id="sql分类">SQL分类</h2>
<p><img src="https://czh-pic.oss-cn-guangzhou.aliyuncs.com/202302110112578.png" alt=""></p>
<table>
<thead>
<tr>
<th>DDL</th>
<th>数据库定义语言</th>
<th>create、drop、alter等</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>DML</strong></td>
<td><strong>数据库操作语言</strong></td>
<td><strong>insert、delete、update等</strong></td>
</tr>
<tr>
<td><strong>DQL</strong></td>
<td><strong>数据库查询语言</strong></td>
<td><strong>select、where等</strong></td>
</tr>
<tr>
<td><strong>DCL</strong></td>
<td><strong>数据库控制语言</strong></td>
<td><strong>grant、revoke等</strong></td>
</tr>
</tbody>
</table>
<h2 id="ddl操作数据库表">DDL：操作数据库、表</h2>
<h3 id="操作数据库crud">操作数据库：CRUD</h3>
<h4 id="c-create-创建">C (create): 创建</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> dabatase <span style="color:#960050;background-color:#1e0010">数据库名称</span>;  <span style="color:#75715e">-- 创建数据库
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">database</span> <span style="color:#66d9ef">if</span> <span style="color:#66d9ef">not</span> <span style="color:#66d9ef">exists</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span>;  <span style="color:#75715e">-- 创建数据库，判断不存在再创建
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">database</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span> <span style="color:#66d9ef">character</span> <span style="color:#66d9ef">set</span> <span style="color:#960050;background-color:#1e0010">字符集名</span>; <span style="color:#f92672">--</span><span style="color:#960050;background-color:#1e0010">创建数据库，指定字符集</span>
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">database</span> <span style="color:#66d9ef">if</span> <span style="color:#66d9ef">not</span> <span style="color:#66d9ef">exists</span> DB <span style="color:#66d9ef">character</span> <span style="color:#66d9ef">set</span> gbk; <span style="color:#75715e">-- 指定字符集gbk，判断是否存在再创建
</span></span></span></code></pre></div><h4 id="r-retieve-查询">R (retieve): 查询</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">show</span> <span style="color:#66d9ef">databases</span>;  <span style="color:#75715e">-- 查询数据库名称
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">show</span> <span style="color:#66d9ef">create</span> <span style="color:#66d9ef">database</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span>; <span style="color:#75715e">-- 查询某个数据库的字符集：查询某个数据库的创建语句
</span></span></span></code></pre></div><h4 id="u-update-修改">U (update): 修改</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">database</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span> <span style="color:#66d9ef">character</span> <span style="color:#66d9ef">set</span> <span style="color:#960050;background-color:#1e0010">字符集名称</span>;  <span style="color:#f92672">--</span><span style="color:#960050;background-color:#1e0010">修改数据库的字符集</span>
</span></span></code></pre></div><h4 id="d-delete-删除">D (delete): 删除</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">database</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span>; <span style="color:#75715e">-- 删除数据库
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">database</span> <span style="color:#66d9ef">if</span> <span style="color:#66d9ef">exists</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span>; <span style="color:#75715e">-- 判断数据库存在，存在再删除
</span></span></span></code></pre></div><h4 id="使用数据库">使用数据库</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">select</span> <span style="color:#66d9ef">database</span>(); <span style="color:#75715e">-- 查询正在使用的数据库
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">use</span> <span style="color:#960050;background-color:#1e0010">数据库名称</span>; <span style="color:#75715e">-- 使用数据库
</span></span></span></code></pre></div><h3 id="操作表">操作表</h3>
<h4 id="增加">增加</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>		<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">1</span>,
</span></span><span style="display:flex;"><span>    	<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">2</span>,
</span></span><span style="display:flex;"><span>    	...
</span></span><span style="display:flex;"><span>    	<span style="color:#960050;background-color:#1e0010">列名</span>n <span style="color:#960050;background-color:#1e0010">数据类型</span>n  <span style="color:#75715e">-- 最后一列不加逗号
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>);
</span></span></code></pre></div><p>常用mysql数据库类型：</p>
<table>
<thead>
<tr>
<th style="text-align:center">int</th>
<th style="text-align:center">整数类型</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center"><strong>double</strong></td>
<td style="text-align:center"><strong>小数类型</strong></td>
</tr>
<tr>
<td style="text-align:center"><strong>date</strong></td>
<td style="text-align:center"><strong>日期，只包括年月日</strong></td>
</tr>
<tr>
<td style="text-align:center"><strong>timestamp</strong></td>
<td style="text-align:center"><strong>时间戳类型，包括年月日时分秒（如果不赋值，默认系统时间）</strong></td>
</tr>
<tr>
<td style="text-align:center"><strong>datetimp</strong></td>
<td style="text-align:center"><strong>日期，包括年月日时分秒</strong></td>
</tr>
<tr>
<td style="text-align:center"><strong>varchar</strong></td>
<td style="text-align:center">**字符串，varchar（20）最大20个字符的意思  **</td>
</tr>
</tbody>
</table>
<h4 id="查询">查询</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">show</span> <span style="color:#66d9ef">tables</span>; <span style="color:#75715e">-- 查询某个数据库中的所有表名称
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">desc</span> <span style="color:#960050;background-color:#1e0010">表名</span>; <span style="color:#75715e">-- 查询该表结构
</span></span></span></code></pre></div><h4 id="修改">修改</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> remove <span style="color:#66d9ef">to</span> <span style="color:#960050;background-color:#1e0010">新表名</span>; <span style="color:#75715e">-- 修改表名
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">character</span> <span style="color:#66d9ef">set</span> <span style="color:#960050;background-color:#1e0010">新字符集名称</span>; <span style="color:#75715e">-- 修改该表字符集
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">add</span> <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">数据类型</span>; <span style="color:#75715e">-- 添加一列
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">change</span> <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">新列名</span> <span style="color:#960050;background-color:#1e0010">新数据类型</span>; <span style="color:#75715e">-- 修改列名称，类型
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">新数据类型</span>; <span style="color:#75715e">-- 修改列类型
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">drop</span> <span style="color:#960050;background-color:#1e0010">列名</span>; <span style="color:#75715e">-- 删除列
</span></span></span></code></pre></div><h4 id="删除">删除</h4>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>; <span style="color:#75715e">-- 删除表
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">table</span> <span style="color:#66d9ef">if</span> <span style="color:#66d9ef">exists</span> <span style="color:#960050;background-color:#1e0010">表名</span>; <span style="color:#75715e">-- 判断是否存在，再删除表
</span></span></span></code></pre></div><h2 id="dml增删改表中数据">DML：增删改表中数据</h2>
<h3 id="添加数据">添加数据</h3>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">insert</span> <span style="color:#66d9ef">into</span> <span style="color:#960050;background-color:#1e0010">表名</span>(<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span><span style="color:#960050;background-color:#1e0010">，列名</span><span style="color:#ae81ff">2</span>...<span style="color:#960050;background-color:#1e0010">列名</span>n) <span style="color:#66d9ef">values</span> (<span style="color:#960050;background-color:#1e0010">值</span><span style="color:#ae81ff">1</span><span style="color:#960050;background-color:#1e0010">，值</span><span style="color:#ae81ff">2</span>...<span style="color:#960050;background-color:#1e0010">值</span>n);
</span></span></code></pre></div><h3 id="删除数据">删除数据</h3>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">delete</span> <span style="color:#66d9ef">from</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">where</span> <span style="color:#960050;background-color:#1e0010">条件</span>;
</span></span></code></pre></div><p><code>注意:</code></p>
<pre><code>1. 如果不加条件，则删除表中所有记录
2. 如果要删除所有记录：
 +   `delete from 表名； -- 不推荐，逐条删除，效率不高`
 +   `truncate table 表名; -- 推荐，先删除整张表，再建一个一样的表`
</code></pre>
<h3 id="修改数据">修改数据</h3>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">update</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">set</span> <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span> <span style="color:#f92672">=</span> <span style="color:#960050;background-color:#1e0010">值</span><span style="color:#ae81ff">1</span>, <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span> <span style="color:#f92672">=</span> <span style="color:#960050;background-color:#1e0010">值</span><span style="color:#ae81ff">2</span> ...<span style="color:#66d9ef">where</span> <span style="color:#960050;background-color:#1e0010">条件</span>;
</span></span></code></pre></div><p><code>注意:</code>如果不加任何条件，则将表中该列所有数据全部修改</p>
<h2 id="dql查询表中的记录">DQL：查询表中的记录</h2>
<h3 id="语法">语法：</h3>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">select</span>	
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">字段列表</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">from</span>
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">表名列表</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">where</span>
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">条件列表</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">group</span> <span style="color:#66d9ef">by</span>
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">分组字段</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">having</span> 
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">分组之后的条件</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">order</span> <span style="color:#66d9ef">by</span>
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">排序</span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">limit</span>
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">分页限定</span>
</span></span></code></pre></div><h3 id="基础查询">基础查询</h3>
<ol>
<li>
<p>多个字段查询：</p>
<p><code>select 字段名1，字段名2...字段名n from 表名;</code></p>
<p><code>注意：</code>如果查询所有字段可以使用 * 来代替字段列表</p>
</li>
<li>
<p>去除重复：</p>
<p><code>distinct</code></p>
</li>
<li>
<p>计算列：</p>
<ol>
<li>
<p>可以使用四则运算计算一些列的值（一般只会进行数值型的计算）</p>
</li>
<li>
<p>IFNULL(表达式1，表达式2)：NULL参与运算，计算结果都为null</p>
<p>表达式1：哪个字段需要判断是否为null</p>
</li>
</ol>
</li>
<li>
<p>起别名：</p>
<p>as: as也可省略</p>
</li>
</ol>
<h3 id="条件查询">条件查询</h3>
<ol>
<li>
<p>where 子句后跟条件</p>
</li>
<li>
<p>运算符:</p>
<p><code> &lt;, &gt;, =, &gt;=, &lt;=, =, &lt;&gt;（不等值查询，例如id&lt;&gt;1,查询id不等于1）</code></p>
<p><code>between..and..（底层是in）</code></p>
<p><code>IN(集合)</code></p>
<p><code>LIKE</code>: 模糊查询==&gt; 占位符&quot; _&quot;（下划线）,&quot;%&quot; <code>例如：NAME LIKE '%陈%'</code></p>
<p><code>IS NULL</code></p>
<p><code>and 或 &amp;&amp;</code></p>
<p><code>or 或 ||</code></p>
<p><code>not 或 |</code></p>
<p><strong>运算符优先级：</strong></p>
<p>() &gt; 算数运算符（+ - * /） &gt; 所有的关系运算符 &gt; NOT &gt; AND &gt; OR</p>
</li>
</ol>
<h3 id="排序查询">排序查询</h3>
<ol>
<li>
<p>语法：</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">order</span> <span style="color:#66d9ef">by</span> <span style="color:#960050;background-color:#1e0010">子句</span>;
</span></span><span style="display:flex;"><span>
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">order</span> <span style="color:#66d9ef">by</span> <span style="color:#960050;background-color:#1e0010">排序字段</span><span style="color:#ae81ff">1</span> <span style="color:#960050;background-color:#1e0010">排序方式</span><span style="color:#ae81ff">1</span>, <span style="color:#960050;background-color:#1e0010">排序字段</span><span style="color:#ae81ff">2</span> <span style="color:#960050;background-color:#1e0010">排序方式</span><span style="color:#ae81ff">2</span>...;
</span></span></code></pre></div></li>
<li>
<p>排序方式：</p>
<p>ASC：升序，默认的</p>
<p>DESC：降序</p>
</li>
<li>
<p><code>注意：</code>如果多个条件，则前边条件值一样时，才会判断下一个条件</p>
</li>
</ol>
<h3 id="聚合函数">聚合函数</h3>
<table>
<thead>
<tr>
<th style="text-align:center">函数名</th>
<th style="text-align:center">说明</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align:center">count</td>
<td style="text-align:center">计算个数；一般选择非空的列（主键）；count(*)</td>
</tr>
<tr>
<td style="text-align:center">max</td>
<td style="text-align:center">计算最大值</td>
</tr>
<tr>
<td style="text-align:center">min</td>
<td style="text-align:center">计算最小值</td>
</tr>
<tr>
<td style="text-align:center">sum</td>
<td style="text-align:center">计算和</td>
</tr>
<tr>
<td style="text-align:center">avg</td>
<td style="text-align:center">计算平均值</td>
</tr>
</tbody>
</table>
<p>聚合函数计算，排除null值</p>
<p>解决方案：</p>
<ol>
<li>选择不包含非空列的计算</li>
<li>IFNULL函数</li>
</ol>
<h3 id="分组查询">分组查询</h3>
<ol>
<li>语法：<code>group by 分组字段;</code></li>
<li><code>注意：</code>
<ol>
<li>分组之后查询的字段：分组字段，聚合函数</li>
<li>where 和 having 的区别：
<ul>
<li>where 在分组前进行限定，如果不满足条件，则不参与分组；</li>
<li>having 在分组后进行限定，如果不满足条件，则不会被查出来</li>
<li>where 后不可以接聚合函数，having 后可以接聚合函数判断</li>
</ul>
</li>
</ol>
</li>
</ol>
<h3 id="分页查询">分页查询</h3>
<ol>
<li>语法：<code>limit 开始索引， 每页查询的条数；</code></li>
<li>公式：<code>开始索引 = （当前的页码 - 1） * 每页显示条数</code></li>
<li>limit 是 mySQL 的&quot;方言&quot;</li>
</ol>
<h3 id="单行函数">单行函数</h3>
<ol>
<li>查询当前时间：<code>NOW()</code></li>
<li>获取日期中的天数：<code>DAY(date)</code></li>
<li>返回time对应的小时数：<code>HOUR(time)</code></li>
<li>返回time对应的月份数：<code>MONTH(date)</code></li>
<li>返回date对应的分钟数：<code>MINUTE(time)</code></li>
<li>返回date对应的年份：<code>YEAR(date)</code></li>
<li>获取一个日期或日期时间值：<code>LAST_DAY(date)</code></li>
</ol>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#75715e">#查询当前时间
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">now</span>(); <span style="color:#75715e">-- 2022-01-04 17:30:18
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#获取日期中的天数，范围是从1到31(几号)
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">day</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 4
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#返回time对应的小时数，范围是从0到23
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">hour</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 17
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#返回time对应的月份数，范围是从1到12
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">month</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 1
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#返回date对应的分钟数，范围是从0到59
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">minute</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 31
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#返回date对应的年份，范围是从1000到9999
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#66d9ef">year</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 2022
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span>
</span></span><span style="display:flex;"><span><span style="color:#75715e">#获取一个日期或日期时间值，返回该月最后一天
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">last_day</span>(<span style="color:#a6e22e">now</span>()); <span style="color:#75715e">-- 2022-01-31
</span></span></span></code></pre></div><p><strong>日期转换成字符串：DATE_FORMAT(date, format)</strong></p>
<table>
<thead>
<tr>
<th>格式</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>%Y</td>
<td>年份，数字形式，4位数</td>
</tr>
<tr>
<td>%m</td>
<td>月份，数字形式（00-12）</td>
</tr>
<tr>
<td>%d</td>
<td>该月日期，数字形式（00-31）</td>
</tr>
<tr>
<td>%H</td>
<td>小时（00-23）</td>
</tr>
<tr>
<td>%i</td>
<td>分钟，数字形式（00-59）</td>
</tr>
<tr>
<td>%s</td>
<td>秒（00-59）</td>
</tr>
</tbody>
</table>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">select</span> <span style="color:#a6e22e">date_format</span>(<span style="color:#a6e22e">now</span>(), <span style="color:#e6db74">&#39;%Y-%m-%d&#39;</span>); <span style="color:#75715e">-- 2022-01-04
</span></span></span></code></pre></div><p><strong>字符串转换为日期：STR_TO_DATE(str, format)</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#a6e22e">str_to_date</span>(<span style="color:#e6db74">&#39;2022年-01月-04日&#39;</span>, <span style="color:#e6db74">&#39;%Y-%m-%d&#39;</span>);
</span></span></code></pre></div><h2 id="约束">约束</h2>
<h3 id="非空约束not-null">非空约束：not null</h3>
<p><strong>创建表时添加约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">1</span>,
</span></span><span style="display:flex;"><span>    <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">2</span> <span style="color:#66d9ef">not</span> <span style="color:#66d9ef">null</span>
</span></span><span style="display:flex;"><span>);
</span></span></code></pre></div><p><strong>创建表完后，添加非空约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">数据类型</span> <span style="color:#66d9ef">not</span> <span style="color:#66d9ef">null</span>;
</span></span></code></pre></div><p><strong>删除非空约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">数据类型</span>;
</span></span></code></pre></div><h3 id="唯一约束unique">唯一约束：unique</h3>
<p><strong>创建表时添加唯一约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">1</span>,
</span></span><span style="display:flex;"><span>    <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">2</span> <span style="color:#66d9ef">unique</span>
</span></span><span style="display:flex;"><span>);
</span></span></code></pre></div><p><strong>删除唯一约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">index</span> <span style="color:#960050;background-color:#1e0010">列名</span>;
</span></span></code></pre></div><p><strong>在创建表后，添加唯一约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">数据类型</span> <span style="color:#66d9ef">unique</span>;
</span></span></code></pre></div><h3 id="添加索引index">添加索引：index</h3>
<p><strong>添加索引</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">index</span> <span style="color:#960050;background-color:#1e0010">索引名</span> <span style="color:#66d9ef">on</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">排序方式</span>,
</span></span><span style="display:flex;"><span>    <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">排序方式</span>
</span></span><span style="display:flex;"><span>);
</span></span></code></pre></div><p><strong>删除索引</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">index</span> <span style="color:#960050;background-color:#1e0010">索引名</span>;
</span></span></code></pre></div><h3 id="主键约束primary-key">主键约束：primary key</h3>
<p><code>**注意：**</code></p>
<ol>
<li>含义：非空且唯一</li>
<li>一张表中只能有一个字段为主键</li>
<li>主键就是表中记录的唯一标识</li>
</ol>
<p><strong>创建表时，添加主键约束：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>	<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">1</span> <span style="color:#66d9ef">primary</span> <span style="color:#66d9ef">key</span>,
</span></span><span style="display:flex;"><span>    <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span> <span style="color:#960050;background-color:#1e0010">数据类型</span><span style="color:#ae81ff">2</span> 
</span></span><span style="display:flex;"><span>);
</span></span></code></pre></div><p><strong>删除主键：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">primary</span> <span style="color:#66d9ef">key</span>;
</span></span></code></pre></div><p><strong>创建表后，添加主键：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify <span style="color:#960050;background-color:#1e0010">列名</span> <span style="color:#960050;background-color:#1e0010">数据类型</span> <span style="color:#66d9ef">primary</span> <span style="color:#66d9ef">key</span>;
</span></span></code></pre></div><h3 id="自主增长auto_increment">自主增长：auto_increment</h3>
<p><strong>概念：</strong></p>
<p>​	如果某一列是数值类型的，使用auto_increment可以用来完成自动增长</p>
<p><strong>创建表时添加主键约束，并完成主键自动增长</strong></p>
<p>​	自动增长多用于主键，故以主键 id 为例</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> (
</span></span><span style="display:flex;"><span>	id <span style="color:#66d9ef">int</span> <span style="color:#66d9ef">primary</span> <span style="color:#66d9ef">key</span> <span style="color:#66d9ef">auto_increment</span>,
</span></span><span style="display:flex;"><span>    name <span style="color:#66d9ef">varchar</span>(<span style="color:#ae81ff">20</span>)
</span></span><span style="display:flex;"><span>);
</span></span></code></pre></div><p><strong>删除自动增长</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify id <span style="color:#66d9ef">int</span>;
</span></span></code></pre></div><p><strong>创建表后，添加自动增长</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> modify id <span style="color:#66d9ef">int</span> <span style="color:#66d9ef">auto_increment</span>;
</span></span></code></pre></div><h3 id="外键约束foreign-key">外键约束：foreign key</h3>
<p><strong>创建表时，添加外键：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span>(
</span></span><span style="display:flex;"><span>	...
</span></span><span style="display:flex;"><span>    <span style="color:#960050;background-color:#1e0010">外键列</span>
</span></span><span style="display:flex;"><span>    constrain <span style="color:#960050;background-color:#1e0010">外键名称</span> <span style="color:#66d9ef">foreign</span> <span style="color:#66d9ef">key</span> (<span style="color:#960050;background-color:#1e0010">外键列名称</span>) <span style="color:#66d9ef">references</span> <span style="color:#960050;background-color:#1e0010">主表名称</span>(<span style="color:#960050;background-color:#1e0010">主表列名称</span>)
</span></span><span style="display:flex;"><span>)
</span></span></code></pre></div><p><strong>删除外键：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">drop</span> <span style="color:#66d9ef">foreign</span> <span style="color:#66d9ef">key</span> <span style="color:#960050;background-color:#1e0010">外键名称</span>;
</span></span></code></pre></div><p><strong>创建表后，添加外键：</strong></p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">add</span> constrain <span style="color:#960050;background-color:#1e0010">外键名称</span> <span style="color:#66d9ef">foreign</span> <span style="color:#66d9ef">key</span> (<span style="color:#960050;background-color:#1e0010">外键列名称</span>) <span style="color:#66d9ef">references</span> <span style="color:#960050;background-color:#1e0010">主表名称</span>(<span style="color:#960050;background-color:#1e0010">主表列名称</span>);
</span></span></code></pre></div><p><strong>级联操作：</strong></p>
<ul>
<li>
<p>添加级联操作：</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">alter</span> <span style="color:#66d9ef">table</span> <span style="color:#960050;background-color:#1e0010">表名</span> <span style="color:#66d9ef">add</span> constrain <span style="color:#960050;background-color:#1e0010">外键名称</span> <span style="color:#66d9ef">foreign</span> <span style="color:#66d9ef">key</span> (<span style="color:#960050;background-color:#1e0010">外键列名称</span>) <span style="color:#66d9ef">references</span> <span style="color:#960050;background-color:#1e0010">主表名称</span>(<span style="color:#960050;background-color:#1e0010">主表列名称</span>) <span style="color:#66d9ef">on</span> <span style="color:#66d9ef">update</span> <span style="color:#66d9ef">cascade</span>;
</span></span></code></pre></div></li>
<li>
<p>分类：</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">on</span> <span style="color:#66d9ef">update</span> <span style="color:#66d9ef">cascade</span>; <span style="color:#75715e">-- 级联更新
</span></span></span><span style="display:flex;"><span><span style="color:#75715e"></span><span style="color:#66d9ef">on</span> <span style="color:#66d9ef">delete</span> <span style="color:#66d9ef">cascade</span>; <span style="color:#75715e">-- 级联删除
</span></span></span></code></pre></div></li>
</ul>
<h3 id="建立试图">建立试图</h3>
<p>格式：</p>
<div class="highlight"><pre tabindex="0" style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4;"><code class="language-mysql" data-lang="mysql"><span style="display:flex;"><span><span style="color:#66d9ef">create</span> view <span style="color:#960050;background-color:#1e0010">视图名</span>(<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span><span style="color:#960050;background-color:#1e0010">，</span> <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span><span style="color:#960050;background-color:#1e0010">，</span> <span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">3</span>...) 
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">as</span> 
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">select</span>  <span style="color:#960050;background-color:#1e0010">表名</span><span style="color:#ae81ff">1</span>.<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">1</span><span style="color:#960050;background-color:#1e0010">，</span> <span style="color:#960050;background-color:#1e0010">表名</span><span style="color:#ae81ff">1</span>.<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">2</span><span style="color:#960050;background-color:#1e0010">，</span> <span style="color:#960050;background-color:#1e0010">表名</span><span style="color:#ae81ff">2</span>.<span style="color:#960050;background-color:#1e0010">列名</span><span style="color:#ae81ff">3</span>...
</span></span><span style="display:flex;"><span><span style="color:#66d9ef">from</span> <span style="color:#960050;background-color:#1e0010">表名</span><span style="color:#ae81ff">1</span><span style="color:#960050;background-color:#1e0010">，表名</span><span style="color:#ae81ff">2</span><span style="color:#960050;background-color:#1e0010">；</span>
</span></span></code></pre></div>
    <h4><a href="https://czh-dev.gitee.io/czh-blog.gitee.io/">Back to Home</a></h4>
  </div>

  <div class="span3 bs-docs-sidebar" style="position:fixed;right: 40px;top: 50px;">
    <h1>catalogue</h1>
    <ul class="nav nav-list bs-docs-sidenav">
      <div class="toc-div">
        <nav id="TableOfContents">
  <ul>
    <li><a href="#sql分类">SQL分类</a></li>
    <li><a href="#ddl操作数据库表">DDL：操作数据库、表</a>
      <ul>
        <li><a href="#操作数据库crud">操作数据库：CRUD</a></li>
        <li><a href="#操作表">操作表</a></li>
      </ul>
    </li>
    <li><a href="#dml增删改表中数据">DML：增删改表中数据</a>
      <ul>
        <li><a href="#添加数据">添加数据</a></li>
        <li><a href="#删除数据">删除数据</a></li>
        <li><a href="#修改数据">修改数据</a></li>
      </ul>
    </li>
    <li><a href="#dql查询表中的记录">DQL：查询表中的记录</a>
      <ul>
        <li><a href="#语法">语法：</a></li>
        <li><a href="#基础查询">基础查询</a></li>
        <li><a href="#条件查询">条件查询</a></li>
        <li><a href="#排序查询">排序查询</a></li>
        <li><a href="#聚合函数">聚合函数</a></li>
        <li><a href="#分组查询">分组查询</a></li>
        <li><a href="#分页查询">分页查询</a></li>
        <li><a href="#单行函数">单行函数</a></li>
      </ul>
    </li>
    <li><a href="#约束">约束</a>
      <ul>
        <li><a href="#非空约束not-null">非空约束：not null</a></li>
        <li><a href="#唯一约束unique">唯一约束：unique</a></li>
        <li><a href="#添加索引index">添加索引：index</a></li>
        <li><a href="#主键约束primary-key">主键约束：primary key</a></li>
        <li><a href="#自主增长auto_increment">自主增长：auto_increment</a></li>
        <li><a href="#外键约束foreign-key">外键约束：foreign key</a></li>
        <li><a href="#建立试图">建立试图</a></li>
      </ul>
    </li>
  </ul>
</nav>
      </div>
    </ul>
  </div>

</div>
<script src="https://cdn.jsdelivr.net/npm/gumshoejs@5.1.2/dist/gumshoe.min.js"></script>
<script>
  var spy = new Gumshoe('#TableOfContents a', {
    nested: true,
    nestedClass: 'active'
  });
</script>
<style>
   
  #TableOfContents li,
  #TableOfContents ul {
    list-style-type: none;
  }

  #TableOfContents ul {
    padding-left: 0px;
  }

  #TableOfContents li>a {
    display: block;
    padding: 4px 20px;
    font-size: 95%;
    color: #000000;
  }

  #TableOfContents li>a:hover,
  #TableOfContents li>a:focus {
    padding-left: 19px;
    color: #3A6bA5;
    text-decoration: none;
    background-color: transparent;
    border-left: 1px solid #3A6bA5;
  }

  #TableOfContents li.active>a,
  #TableOfContents li.active>a:hover,
  #TableOfContents li.active>a:focus {
    padding-left: 18px;
    font-weight: bold;
    color: #3A6bA5;
    background-color: transparent;
    border-left: 2px solid #3A6bA5;
  }

   
  #TableOfContents li>ul {
    padding-bottom: 10px;
  }

  #TableOfContents li li>a {
    padding-top: 1px;
    padding-bottom: 1px;
    padding-left: 30px;
    font-size: 14px;
    font-weight: normal;
  }

  #TableOfContents li li>a:hover,
  #TableOfContents li li>a:focus {
    padding-left: 29px;
  }

  #TableOfContents li li.active>a,
  #TableOfContents li li.active>a:hover,
  #TableOfContents li li.active>a:focus {
    padding-left: 28px;
    font-weight: 500;
  }

  #TableOfContents .nav-link.active+ul {
    display: block;
  }

  #TableOfContents li>ul {
    display: none;
  }

  #TableOfContents li.active>ul {
    display: inherit;
  }

  .toc-div {
    position: -webkit-sticky;
     
    position: sticky;
     
    top: 20px;
  }
</style>


        </div><footer class="container">
    <hr class="soften">
    <p>
    <a href="https://space.bilibili.com/1799809923">Love eating fried pork ribs</a> | 

&copy; 
<a href="http://jmf-portfolio.netlify.com" target="_blank">
    JM Fergeau
</a>
<span id="thisyear">2023</span>

    | My site


        | Built on <a href="//gohugo.io" target="_blank">Hugo</a>

</p>
    <p class="text-center">
        <a href="https://facebook.com">Facebook</a> 
        <a href="https://twitter.com">Twitter</a> 
        <a href="https://linkedin.com">Linkedin</a> 
        <a href="https://github.com">GitHub</a> 
        <a href="https://gitlab.com">GitLab</a>
    </p>
</footer>

</body><link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap-responsive.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/style.css">

<script src="/czh-blog.gitee.io/js/jquery.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-386.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-transition.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-alert.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-modal.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-dropdown.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-scrollspy.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tab.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tooltip.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-popover.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-button.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-collapse.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-carousel.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-typeahead.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-affix.js"></script>
<script>
    _386 = { 
        fastLoad: false ,
        onePass: false , 
        speedFactor: 1 
    };

    
    function ThisYear() {
        document.getElementById('thisyear').innerHTML = new Date().getFullYear();
    };
</script>
</html>
